1 Imports System.Data.SqlClient
2 Imports Excel = Microsoft.Office.Interop.Excel
3 Public Class frmLogs
4
5
6 Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
7 Me.Close()
8 End Sub
9 Sub fillCombo()
10 Try
11 Dim CN As New SqlConnection(cs)
12 CN.Open()
13 adp = New SqlDataAdapter()
14 adp.SelectCommand = New SqlCommand("SELECT distinct RTRIM(UserID) FROM Registration", CN)
15 ds = New DataSet("ds")
16 adp.Fill(ds)
17 Dim dtable As DataTable = ds.Tables(0)
18 cmbUserID.Items.Clear()
19 For Each drow As DataRow In dtable.Rows
20 cmbUserID.Items.Add(drow(0).ToString())
21 Next
22
23 Catch ex As Exception
24 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
25 End Try
26 End Sub
27
28 Private Sub cmbUserID_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbUserID.SelectedIndexChanged
29 Try
30 con = New SqlConnection(cs)
31 con.Open()
32 cmd = New SqlCommand("SELECT RTRIM(UserID),RTRIM(Date),RTRIM(Operation) from Logs where UserID='" & cmbUserID.Text & "' order by date", con)
33 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
34 dgw.Rows.Clear()
35 While (rdr.Read() = True)
36 dgw.Rows.Add(rdr(0), rdr(1), rdr(2))
37 End While
38 con.Close()
39 Catch ex As Exception
40 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
41 End Try
42 End Sub
43 Private Sub GetData()
44 Try
45 con = New SqlConnection(cs)
46 con.Open()
47 cmd = New SqlCommand("SELECT RTRIM(UserID),RTRIM(Date),RTRIM(Operation) from Logs order by Date", con)
48 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
49 dgw.Rows.Clear()
50 While (rdr.Read() = True)
51 dgw.Rows.Add(rdr(0), rdr(1), rdr(2))
52 End While
53 con.Close()
54 Catch ex As Exception
55 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
56 End Try
57 End Sub
58 Private Sub frmLogs_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
59 fillCombo()
60 GetData()
61 End Sub
62 Sub Reset()
63 cmbUserID.Text = ""
64 dtpDateFrom.Text = Today
65 dtpDateTo.Text = Now
66 GetData()
67 fillCombo()
68 End Sub
69 Private Sub btnReset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReset.Click
70 Reset()
71 End Sub
72
73 Private Sub btnGetData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGetData.Click
74 Try
75 con = New SqlConnection(cs)
76 con.Open()
77 cmd = New SqlCommand("SELECT RTRIM(UserID),RTRIM(Date),RTRIM(Operation) from logs where Date between @date1 and @date2 order by Date", con)
78 cmd.Parameters.Add("@date1", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
79 cmd.Parameters.Add("@date2", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value
80 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
81 dgw.Rows.Clear()
82 While (rdr.Read() = True)
83 dgw.Rows.Add(rdr(0), rdr(1), rdr(2))
84 End While
85 con.Close()
86 Catch ex As Exception
87 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
88 End Try
89 End Sub
90
91 Private Sub btnClose_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
92 Me.Close()
93 End Sub
94
95 Private Sub btnExportExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExportExcel.Click
96 Dim rowsTotal, colsTotal As Short
97 Dim I, j, iC As Short
98 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
99 Dim xlApp As New Excel.Application
100 Try
101 Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
102 Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
103 xlApp.Visible = True
104
105 rowsTotal = dgw.RowCount
106 colsTotal = dgw.Columns.Count - 1
107 With excelWorksheet
108 .Cells.Select()
109 .Cells.Delete()
110 For iC = 0 To colsTotal
111 .Cells(1, iC + 1).Value = dgw.Columns(iC).HeaderText
112 Next
113 For I = 0 To rowsTotal - 1
114 For j = 0 To colsTotal
115 .Cells(I + 2, j + 1).value = dgw.Rows(I).Cells(j).Value
116 Next j
117 Next I
118 .Rows("1:1").Font.FontStyle = "Bold"
119 .Rows("1:1").Font.Size = 12
120
121 .Cells.Columns.AutoFit()
122 .Cells.Select()
123 .Cells.EntireColumn.AutoFit()
124 .Cells(1, 1).Select()
125 End With
126 Catch ex As Exception
127 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
128 Finally
129 'RELEASE ALLOACTED RESOURCES
130 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
131 xlApp = Nothing
132 End Try
133 End Sub
134
135 Private Sub dgw_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
136 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
137 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
138 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
139 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
140 End If
141 Dim b As Brush = SystemBrushes.ControlText
142 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
143
144 End Sub
145 Public Sub DeleteRecord()
146 Try
147 Dim RowsAffected As Integer = 0
148 con.Open()
149 Dim ct As String = "delete from logs"
150 cmd = New SqlCommand(ct)
151 cmd.Connection = con
152 RowsAffected = cmd.ExecuteNonQuery()
153 If con.State = ConnectionState.Open Then
154 con.Close()
155 End If
156 If RowsAffected > 0 Then
157 Dim st As String = "deleted the all logs till date '" & Now & "'"
158 LogFunc(lblUser.Text, st)
159 MessageBox.Show("Successfully deleted", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
160 Reset()
161 Reset()
162 GetData()
163 Else
164 MessageBox.Show("No record found", "Sorry", MessageBoxButtons.OK, MessageBoxIcon.Information)
165 Reset()
166 End If
167 con.Close()
168 Catch ex As Exception
169 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
170 End Try
171 End Sub
172 Private Sub btnDeleteAllLogs_Click(sender As System.Object, e As System.EventArgs) Handles btnDeleteAllLogs.Click
173 Try
174 If MessageBox.Show("Do you really want to delete all logs?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.Yes Then
175 DeleteRecord()
176 End If
177 Catch ex As Exception
178 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
179 End Try
180 End Sub
181 End Class
